﻿GO

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'up_DistributionInspVisitToDistributionAsset')
	DROP PROCEDURE up_DistributionInspVisitToDistributionAsset
GO


CREATE PROCEDURE [dbo].[up_DistributionInspVisitToDistributionAsset]
(
	@vendorProjectId	int
) 
AS

DECLARE @UtilityId int
DECLARE @UtilityProjectId int

--Gets the Utility ID
SELECT 
	@UtilityId = UtilityFk
FROM 
	tb_vendor_Projects
WHERE  
	Vendor_ProjectId = @vendorProjectId

--Gets the Utility Project Id
SELECT 
	@UtilityProjectId = uProject.Utility_ProjectId
FROM 
	tb_vendor_Projects vProject
INNER JOIN tb_utility_projects  uProject
	ON vProject.VendorFk = uProject.VendorFk
	AND vProject.ProjectTypeFk = uProject.TypeFk
WHERE  
	vProject.Vendor_ProjectId = @vendorProjectId


PRINT 'Add Assets from visits'

DECLARE @insertedAssetIds TABLE (Utility_Asset_DistributionId int, DistributionPoleId int)

INSERT INTO tb_Utility_Asset_Distributions
(
	UtilityFK,
	Latitude,
	Longitude,
	Altitude,
	SatelliteCount,
	SatelliteTime,
	SolutionAvailable,
	Handheld,
	Distribution_TopTypeFk,
	Utility_ContactFK,
	Date,
	Utility_ProjectFK,
	MapFk,
	SectionFk,
	[Year],
	TreatmentFk,
	ClassFk,
	SpeciesFk,
	Length,
	Number,
	CreatedOn,
	CreatedBy,
	UtilityKey,
	UtilityGroup,
	D804
)
OUTPUT INSERTED.Utility_Asset_DistributionId, INSERTED.Altitude INTO @insertedAssetIds
SELECT 
	@UtilityId,
	poles.Latitude,
	poles.Longitude,
	poles.Vendor_Distribution_Poles_PoleID, -- TODO: Change this, Using Altitude as temp id
	NULL,
	NULL,
	NULL,
	NULL,
	NULL,
	poles.InspectorFk,
	GETDATE(),
	@UtilityProjectId,
	poles.MapFk,
	poles.SectionFk,
	poles.[Year],
	poles.TreatmentFk,
	poles.ClassFK,
	poles.SpecieFk,
	poles.LengthFk,
	poles.Number,
	NULL,
	'up_DistributionInspVisitToDistributionAsset',
	NULL,
	NULL,
	D804
FROM 
	tb_Vendor_Distribution_Poles poles
WHERE
	poles.ProjectFk = @vendorProjectId
	AND poles.Utility_Asset_Distributions_PoleFk IS NULL

PRINT 'Updates vendor distribution pole foreign key to asset'

UPDATE 
	tb_Vendor_Distribution_Poles 
SET
	Utility_Asset_Distributions_PoleFk = [InsertedIds].Utility_Asset_DistributionId
FROM 
	@insertedAssetIds [InsertedIds]
WHERE
	tb_Vendor_Distribution_Poles.Vendor_Distribution_Poles_PoleID = [InsertedIds].DistributionPoleId

UPDATE 
	tb_Utility_Asset_Distributions
SET
	Altitude = NULL
FROM 
	tb_Utility_Asset_Distributions
INNER JOIN @insertedAssetIds [InsertedIds]
	ON tb_Utility_Asset_Distributions.Utility_Asset_DistributionId = [InsertedIds].Utility_Asset_DistributionId

PRINT 'Add asset phases'
-- If one asset has phases assigned, new records are not inserted

INSERT INTO tb_Utility_Asset_Distribution_PhasesXPole
(
	Utility_Asset_DistributionFk,
	PhaseFk
)
SELECT 
	assets.Utility_Asset_DistributionId,
	phases.Vendor_Distribution_PhaseFk
FROM		
	@insertedAssetIds [InsertedIds]
INNER JOIN tb_Utility_Asset_Distributions assets
	ON assets.Utility_Asset_DistributionId = [InsertedIds].Utility_Asset_DistributionId
INNER JOIN tb_Vendor_Distribution_Poles poles
	ON poles.Vendor_Distribution_Poles_PoleID = [InsertedIds].DistributionPoleId
INNER JOIN tb_Vendor_Distribution_PhasesXPole phases
	ON phases.Vendor_Distribution_PoleFk = poles.Vendor_Distribution_Poles_PoleID


PRINT 'Add asset tops'
-- If one asset has phases assigned, new records are not inserted

INSERT INTO tb_Utility_Asset_Distribution_TopsXPole
(
	Utility_Asset_DistributionFk,
	TopTypeFk
)
SELECT 
	assets.Utility_Asset_DistributionId,
	tops.Vendor_Distribution_TopFk
FROM		
	@insertedAssetIds [InsertedIds]
INNER JOIN tb_Utility_Asset_Distributions assets
	ON assets.Utility_Asset_DistributionId = [InsertedIds].Utility_Asset_DistributionId
INNER JOIN tb_Vendor_Distribution_Poles poles
	ON poles.Vendor_Distribution_Poles_PoleID = [InsertedIds].DistributionPoleId
INNER JOIN tb_Vendor_Distribution_TopsXPole tops
	ON tops.Vendor_Distribution_PoleFk = poles.Vendor_Distribution_Poles_PoleID
	
GO

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'DevOnly_DistributionInspVisitToAsset_UnitTestInitialize')
	DROP PROCEDURE DevOnly_DistributionInspVisitToAsset_UnitTestInitialize

CREATE proc [dbo].[DevOnly_DistributionInspVisitToAsset_UnitTestInitialize] 
(
	@dummyUtilityId int output,
	@dummyVendorId int output
)	
AS
SET NOCOUNT ON	

--Creates dummy utility
INSERT INTO tb_Utilities VALUES ('Dummy Utility', 1, 1)
SELECT @dummyUtilityId = @@IDENTITY

--Creates dummy vendor
INSERT INTO tb_Vendors VALUES ('Dummy Vendor', 2, 1)
SELECT @dummyVendorId = @@IDENTITY

--Creates dummy vendor project
INSERT INTO tb_vendor_projects 
(VendorFk, Name, ProjectTypeFk, UtilityFk, ScheduledStartDate, ActualStartDate, ScheduledEndDate, ScheduledCost, ActualCost, TypeFk)
VALUES (@dummyVendorId, 'Dummy distribution project', 3, @dummyUtilityId, GETDATE(), GETDATE(), GETDATE(), 0, 0, 3)

--Creates dummy users
DECLARE @vendorContactId int
DECLARE @utilityContactId int

INSERT INTO tb_vendor_contacts VALUES (@dummyVendorId, 1, 'Dummy name', 'dummy.name', 1, 'password', 1)
SELECT @vendorContactId = @@IDENTITY

INSERT INTO tb_utility_contacts VALUES (@dummyUtilityId, 1, 'Dummy name', 'dummy.name', 1, 'password', 1)
SELECT @utilityContactId = @@IDENTITY

--Gets ids 
DECLARE @vendorProjectId int

SELECT 
	@vendorProjectId = Vendor_ProjectID
FROM 
	tb_vendor_projects
WHERE
	vendorFk = @dummyVendorId

--Select 20 distribution poles from and set as dummy poles
DECLARE @insertedPolesIds TABLE (newVendorDistributionPoleId int)

INSERT INTO tb_Vendor_Distribution_Poles
(
	AcquisitionDate
	,VendorFk
	,InspectorFk
	,ProjectFk
	,Latitude
	,Longitude
)
OUTPUT INSERTED.Vendor_Distribution_Poles_PoleId INTO @insertedPolesIds
SELECT TOP 20 
	AcquisitionDate
	,@dummyVendorId
	,@utilityContactId
	,@vendorProjectId
	,Latitude
	,Longitude
FROM
	tb_Vendor_Distribution_Poles
ORDER BY 
	1 DESC

GO
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'DevOnly_DistributionInspVisitToAsset_UnitTestCleanUp')
	DROP PROCEDURE DevOnly_DistributionInspVisitToAsset_UnitTestCleanUp
	
CREATE proc [dbo].[DevOnly_DistributionInspVisitToAsset_UnitTestCleanUp] 
(
	@dummyUtilityId int,
	@dummyVendorId int
)
AS
/* 	
	Purpose: Clears tables used to test 
*/

SET NOCOUNT ON	

--Delete Distribution poles
DELETE tb_Vendor_Distribution_Poles
WHERE ProjectFk IN (SELECT Vendor_ProjectId FROM tb_Vendor_Projects WHERE VendorFk = @dummyVendorId)

--Delete Distribution assets
DELETE tb_Utility_Asset_Distributions
WHERE UtilityFk = @dummyUtilityId

--Delete users
DELETE tb_vendor_contacts 
WHERE VendorFk = @dummyVendorId

DELETE tb_utility_contacts 
WHERE UtilityFk = @dummyUtilityId

--Delete the project
DELETE tb_vendor_projects
WHERE VendorFk = @dummyVendorId

--Deletes the utility
DELETE tb_Utilities 
WHERE UtilityId = @dummyUtilityId

--Deletes the vendor
DELETE tb_Vendors
WHERE VendorId = @dummyVendorId


GO

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'DevOnly_DistributionInspVisitToAsset_UnitTest')
	DROP PROCEDURE DevOnly_DistributionInspVisitToAsset_UnitTest

CREATE proc [dbo].[DevOnly_DistributionInspVisitToAsset_UnitTest] as
/* 	
	Purpose: Tests stored procedures 
*/
SET NOCOUNT ON	

DECLARE @dummyUtilityId int
DECLARE @dummyVendorId int
DECLARE @x varchar(100)
DECLARE @y varchar(100)

--Initialize data to test
PRINT 'Initializing data to test'
EXEC DevOnly_DistributionInspVisitToAsset_UnitTestInitialize @dummyUtilityId OUTPUT, @dummyVendorId OUTPUT

PRINT 'Dummy utility ID: ' + CONVERT(varchar, @dummyUtilityId)
PRINT 'Dummy vendor ID: ' + CONVERT(varchar, @dummyVendorId)

-- bail on error
if (NOT @@error = 0)
	return(1)

--Executes tests
DECLARE @vendorProjectId int
SELECT @vendorProjectId = Vendor_ProjectId 
FROM tb_vendor_projects
WHERE UtilityFk = @dummyUtilityId

--Gets count of assets that should be created
DECLARE @assetsCount int
DECLARE @polesCount int

SELECT @polesCount = COUNT(*) FROM tb_Vendor_Distribution_Poles WHERE ProjectFK = @vendorProjectId

--Create assets from visits
EXEC up_DistributionInspVisitToDistributionAsset @vendorProjectId, 20

SELECT @assetsCount = COUNT(*) FROM tb_Utility_Asset_Distributions WHERE UtilityFk = @dummyUtilityId

if (@assetsCount <> @polesCount)
begin
	set @x = 'Assets created: ' + convert(varchar(100), @assetsCount)
	set @y = 'Poles should be converted: ' + convert(varchar(100), @polesCount)
	raiserror(505070, 16, 2, 'AreEqual', @x, @y, 'Poles used and assets created should be equal.')
end

SELECT @polesCount = COUNT(*) FROM tb_Vendor_Distribution_Poles poles WHERE ProjectFK = @vendorProjectId AND Utility_Asset_Distributions_PoleFk IS NULL

if (@polesCount <> 0)
begin
	set @y = convert(varchar(100), @polesCount)
	set @x = convert(varchar(100), 0)
	raiserror(505070, 16, 2, 'AreEqual', @x, @y, 'Problem in asset foreign key, it shouldnt be null')
end

--If I execute it again no assets should be created
--Gets poles converted to compare later
DECLARE @polesConverted table (idPole int, idAsset int)

INSERT INTO @polesConverted
(
	idPole,
	idAsset
)
SELECT Vendor_Distribution_Poles_PoleId, Utility_Asset_Distributions_PoleFk
FROM tb_Vendor_Distribution_Poles
WHERE ProjectFK = @vendorProjectId 
AND Utility_Asset_Distributions_PoleFk IS NOT NULL

EXEC up_DistributionInspVisitToDistributionAsset @vendorProjectId, 20

SELECT @polesCount = COUNT(*)
FROM tb_Vendor_Distribution_Poles poles
INNER JOIN @polesConverted polesConverted
	ON poles.Vendor_Distribution_Poles_PoleId = polesConverted.idPole
	AND poles.Utility_Asset_Distributions_PoleFk = polesConverted.idAsset

DECLARE @originalPoles int

SELECT @originalPoles = COUNT(*) FROM @polesConverted

if (@originalPoles <> @polesCount)
begin
	set @y = convert(varchar(100), @polesCount)
	set @x = convert(varchar(100), @originalPoles)
	raiserror(505070, 16, 2, 'AreEqual', @x, @y, 'New Assets where created. It shouldnt happened')
end

--Deletes data used
PRINT 'Deleting data used to test'
EXEC DevOnly_DistributionInspVisitToAsset_UnitTestCleanUp @dummyUtilityId, @dummyVendorId

-- bail on error
if (NOT @@error = 0)
	return(1)


GO





 